package com.blockchain.console.service.financial.achieve;

import java.io.BufferedWriter;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import com.blockchain.common.enums.ChargeStatus;
import com.blockchain.common.enums.ChargeType;
import com.blockchain.common.enums.IsPass;
import com.blockchain.common.enums.TradingType;
import com.blockchain.console.service.financial.CzglManage;
import com.blockchain.console.service.financial.entity.CzglRecord;
import com.blockchain.console.service.financial.query.CzglRecordQuery;
import com.blockchain.framework.config.ConfigureProvider;
import com.blockchain.framework.data.sql.SQLConnectionProvider;
import com.blockchain.framework.service.ServiceFactory;
import com.blockchain.framework.service.ServiceResource;
import com.blockchain.framework.service.exception.LogicalException;
import com.blockchain.framework.service.exception.ParameterException;
import com.blockchain.framework.service.query.ArrayParser;
import com.blockchain.framework.service.query.Paging;
import com.blockchain.framework.service.query.PagingResult;
import com.blockchain.util.EmailUtil;
import com.blockchain.util.StringHelper;
import com.blockchain.util.parser.DateParser;
import com.blockchain.util.parser.EnumParser;
import com.blockchain.variables.P2PConst;
import com.blockchain.variables.defines.SystemVariable;

public class CzglManageImpl extends AbstractFinancialService implements CzglManage {

	public static class RechargeManageFactory implements ServiceFactory<CzglManage> {

		@Override
		public CzglManage newInstance(ServiceResource serviceResource) {
			return new CzglManageImpl(serviceResource);
		}
	}

	public CzglManageImpl(ServiceResource serviceResource) {
		super(serviceResource);

	}

	public PagingResult<CzglRecord> getUserRechargeRecordList(CzglRecordQuery query, Paging paging) throws Throwable {
		StringBuilder sql = new StringBuilder("SELECT T6010.F01 userid,T6010.F02 username, T6011.F02 name,");
		sql.append(" T6033.F03 cjsj,T6033.F04 je,T6033.F05 zt,T6033.F10 czkh,T6033.F11 lx,T6033.F12 sfzd,T6033.F01,T6033.F13,");
		sql.append(" T6033_1.F02 as rate,T6033_1.F03 as cny,T6033.F16");
		sql.append(" FROM T6033 LEFT JOIN T6010 ON T6033.F02=T6010.F01 LEFT JOIN T6011 ON T6010.F01=T6011.F01 ");
		sql.append(" LEFT JOIN T6033_1 ON(T6033.F01=T6033_1.F01)");
		sql.append(" WHERE 1=1");
		ArrayList<Object> parameters = new ArrayList<>();
		if (query != null) {
			SQLConnectionProvider sqlConnectionProvider = getSQLConnectionProvider();
			if (query.getUserid() > 0) {
				sql.append(" AND T6033.F02=?");
				parameters.add(query.getUserid());
			}
			if (!StringHelper.isEmpty(query.getYhhk())) {
				sql.append(" AND T6033.F10 LIKE ?");
				parameters.add(sqlConnectionProvider.allMatch(query.getYhhk()));
			}
			String loginName = query.getLoginName();
			if (!StringHelper.isEmpty(loginName)) {
				sql.append(" AND T6010.F02 LIKE ?");
				parameters.add(sqlConnectionProvider.allMatch(loginName));
			}

			Timestamp timestamp = query.getStartRechargeTime();
			if (timestamp != null) {
				sql.append(" AND DATE(T6033.F03) >= ?");
				parameters.add(timestamp);
			}
			timestamp = query.getEndRechargeTime();
			if (timestamp != null) {
				sql.append(" AND DATE(T6033.F03) <= ?");
				parameters.add(timestamp);
			}
			if (query.getChargeType() != null) {
				sql.append(" AND T6033.F11 = ?");
				parameters.add(query.getChargeType());
			}
			ChargeStatus status = query.getStatus();
			if (status != null) {
				sql.append(" AND T6033.F05 = ?");
				parameters.add(status.name());
			}
			String name = query.getName();
			if (!StringHelper.isEmpty(name)) {
				sql.append(" AND T6011.F02 LIKE ?");
				parameters.add(sqlConnectionProvider.allMatch(name));
			}
			if (query.getZddz() != null) {
				sql.append(" AND T6033.F12=?");
				parameters.add(query.getZddz());
			}
		}
		sql.append(" ORDER BY T6033.F03 DESC");
		return selectPaging(getConnection(P2PConst.DB_USER), new ArrayParser<CzglRecord>() {
			ArrayList<CzglRecord> list = new ArrayList<CzglRecord>();

			public CzglRecord[] parse(ResultSet rst) throws SQLException {
				while (rst.next()) {
					CzglRecord user = new CzglRecord();
					user.userid = rst.getInt(1);
					user.loginName = rst.getString(2);
					user.userName = rst.getString(3);
					user.chargeDateTime = rst.getTimestamp(4);
					user.userAmount = rst.getBigDecimal(5);
					user.rechargeStatus = EnumParser.parse(ChargeStatus.class, rst.getString(6));
					user.czkh = rst.getString(7);
					user.chargeType = EnumParser.parse(ChargeType.class, rst.getString(8));
					user.is = EnumParser.parse(IsPass.class, rst.getString(9));
					user.id = rst.getInt(10);
					user.tjjeAmount = rst.getBigDecimal(11);
					user.rate =  rst.getBigDecimal(12);
					user.cny =  rst.getBigDecimal(13);
					user.approver = getName(rst.getInt(14));
					list.add(user);
				}
				return list.toArray(new CzglRecord[list.size()]);
			}
		}, paging, sql.toString(), parameters);
	}

	@Override
	public void export(CzglRecord[] items, OutputStream outputStream, String charset) throws Throwable {
		if (outputStream == null) {
			return;
		}
		if (StringHelper.isEmpty(charset)) {
			charset = "GBK";
		}
		if (items == null) {
			return;
		}
		try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(outputStream, charset))) {
			writer.write("用户ID");
			writer.write(",");
			writer.write("用户名");
			writer.write(",");
			writer.write("姓名");
			writer.write(",");
			writer.write("银行卡号");
			writer.write(",");
			writer.write("充值金额");
			writer.write(",");
			writer.write("实际到账金额");
			writer.write(",");
			writer.write("充值方式");
			writer.write(",");
			writer.write("充值状态");
			writer.write(",");
			writer.write("充值时间");
			writer.write(",");
			writer.write("是否自动到账");
			writer.newLine();
			for (CzglRecord item : items) {
				if (item == null) {
					continue;
				}
				writer.write(Integer.toString(item.id));
				writer.write(",");
				writer.write(item.loginName == null ? "" : item.loginName);
				writer.write(",");
				writer.write(item.userName == null ? "" : item.userName);
				writer.write(",");
				writer.write(item.czkh == null ? "" : item.czkh);
				writer.write(",");
				writer.write(format(item.userAmount));
				writer.write(",");
				writer.write(format(item.tjjeAmount));
				writer.write(",");
				writer.write(item.chargeType == null ? "" : item.chargeType.getName());
				writer.write(",");
				writer.write(item.rechargeStatus.getName());
				writer.write(",");
				writer.write("" + DateParser.format(item.chargeDateTime, "yyyy-MM-dd HH:mm"));
				writer.write(",");
				writer.write(item.is == null ? "" : item.is.getName());
				writer.newLine();
			}
			writer.flush();
		}

	}

	@Override
	public void czQrdz(int id, int userid) throws Throwable {
		serviceResource.openTransactions();
		try {
			if (id <= 0) {
				throw new ParameterException("ID异常。");
			}
			if (userid <= 0) {
				throw new ParameterException("用户不存在。");
			}
			BigDecimal zhye = selectBigDecimal(P2PConst.DB_USER, "SELECT F02 FROM T6023 WHERE F01=? FOR UPDATE",
					userid);// 用户余额
			ChargeStatus status = EnumParser.parse(ChargeStatus.class, selectString(getConnection(P2PConst.DB_USER),
					"SELECT F05 FROM T6033 WHERE F01=? AND F02=?", id, userid));// 状态
			IsPass is = EnumParser.parse(IsPass.class, selectString(getConnection(P2PConst.DB_USER),
					"SELECT F12 FROM T6033 WHERE F01=? AND F02=?", id, userid));
			BigDecimal sjdzje = selectBigDecimal(getConnection(P2PConst.DB_USER),
					"SELECT F03 FROM T6033_1 WHERE F01=?", id);
			
			// 是否自动到账
			if (status != ChargeStatus.CLZ) {
				throw new ParameterException("状态不是处理中。");
			}
			if (is != IsPass.F) {
				throw new ParameterException("不是手动到账。");
			}

			String bz = "充值:" + sjdzje+"元";
			execute(getConnection(P2PConst.DB_USER), "INSERT INTO T6023 SET F01=?, F02=F02+?,F04=F04+? ON DUPLICATE KEY UPDATE F02=F02+?,F04=F04+?", userid,sjdzje, sjdzje,
					sjdzje,sjdzje);

			execute(getConnection(P2PConst.DB_USER),
					"INSERT INTO T6032 SET F02=?,F03=?,F04=CURRENT_TIMESTAMP(),F05=?,F06=0,F07=?,F08=?,F09=?", userid,
					TradingType.CZ, sjdzje, zhye.add(sjdzje), id, bz);
			execute(getConnection(P2PConst.DB_USER), "UPDATE T6033 SET F04=?,F05=?,F16=?,F08=CURRENT_TIMESTAMP() WHERE F01=? AND F02=? ", sjdzje,ChargeStatus.YDZ,
					serviceResource.getSession().getAccountId(),id, userid);
			log(serviceResource.getSession().getAccountId(), "确认到账:" + id + "实际到账金额：" + sjdzje);

			
			String yqrm = selectString(P2PConst.DB_USER, "SELECT F06 FROM T6011 WHERE F01=?" , userid);
			if (null != yqrm && !yqrm.equals("")) {
				int yqrId = selectInt(P2PConst.DB_USER, "SELECT F01 FROM T6011 WHERE F07=?", yqrm);
				if (yqrId != 0 && sjdzje.compareTo(BigDecimal.ZERO)==1) {
					yqrCzPresent(yqrId,sjdzje);
				}
			}
			
			sms(userid,sjdzje);
		} catch (Throwable e) {
			serviceResource.rollback();
			e.printStackTrace();
			throw new LogicalException(e.getMessage());
		}
	}

	/**
	*  发送短信 2017年8月1日13:47:20
	 * @throws IOException 
	*/
	private void sms(int userid, BigDecimal je) throws SQLException, IOException{
		if(userid>0){
		ConfigureProvider configureProvider = serviceResource.getResource(ConfigureProvider.class);
		String iphone =selectString(P2PConst.DB_USER, "SELECT F02 FROM T6010 WHERE F01=?", userid);// 发送短信
		String english = "Dear "+EmailUtil.NIKENAME+" user, Congratulations, credit SMS_CZ success, please check your balance. <br><br>";
		String s = english + configureProvider.getProperty(SystemVariable.SMS_CZ);
		String content = s.replaceAll("SMS_CZ", je.toString());
		EmailUtil.sendHtmlMail(iphone,EmailUtil.NIKENAME, content+"<br><br><br><br>", content, true);
		
		}
	}		
	@Override
	public String getUsername(int userid) throws Throwable {
		if(userid<=0){
			throw new ParameterException("用户不存在。");	
		}
		return selectString(getConnection(P2PConst.DB_USER), "SELECT F02 FROM T6010 WHERE F01=?", userid);
	}

	@Override
	public void czWdz(int id) throws Throwable {
		if (id <= 0) {
			throw new ParameterException("记录不存在。");
		}
		execute(getConnection(P2PConst.DB_USER), "UPDATE T6033 SET F05=? WHERE F01=?", ChargeStatus.WZF, id);
	}

	/**
	 * 查询审核人
	 * 
	 * @param userId
	 * @return
	 */
	private String getName(int id) throws SQLException {
		try (PreparedStatement pstmt = getConnection().prepareStatement(
				"SELECT F02 FROM T7011 WHERE F01=?")) {
			pstmt.setInt(1, id);
			try (ResultSet resultSet = pstmt.executeQuery()) {
				if (resultSet.next()) {
					return resultSet.getString(1);
				}
			}
		}
		return "";
	}
	
	private void yqrCzPresent(int yqrId,BigDecimal czje) throws Throwable {

		String sql = "SELECT F02 FROM _1010 WHERE F01=?";
		
		BigDecimal txyqrzs = selectBigDecimal(P2PConst.DB_CONFIG, sql, "SYSTEM.CZYQRZS");
		
		if (txyqrzs.compareTo(BigDecimal.ZERO)!=1) {
			return;
		}
		BigDecimal zsje = czje.multiply(txyqrzs.divide(new BigDecimal("100"),4,RoundingMode.HALF_UP));
		execute(getConnection(P2PConst.DB_USER),
				"INSERT INTO T6023 SET F01=?,F02=?,F04=? ON DUPLICATE KEY UPDATE F02=F02+?,f03=0,F04=F04+?",
				yqrId,zsje, zsje, zsje, zsje);
			
	}
}
